Gantt-chart in BIP 11g

Gantt-chart in BIP 11g

BI Publisher with JFreeChart an Open Source plotting API in order to generate plots that are not provided by BI-Publisher such as the Gantt chart type. The main steps are as follows:
1) Create the BIP data model: as an example I used the sample Gantt data mentioned below:

create table activities
( label varchar2(100)
, start_date date
, end_date date
);

insert into activities
( label, start_date, end_date )
values
( ‘Functional Analysis’
, to_date(’12-03-2001′,’DD-MM-YYYY’)
, to_date(’12-11-2001′,’DD-MM-YYYY’)
)
/
insert into activities
( label, start_date, end_date )
values
( ‘Technical Architecture’
, to_date(’04-05-2001′,’DD-MM-YYYY’)
, to_date(’18-08-2002′,’DD-MM-YYYY’)
)
/
insert into activities
( label, start_date, end_date )
values
( ‘Technical Design’
, to_date(’01-12-2001′,’DD-MM-YYYY’)
, to_date(’24-06-2002′,’DD-MM-YYYY’)
)
/
insert into activities
( label, start_date, end_date )
values
( ‘Development’
, to_date(’01-05-2002′,’DD-MM-YYYY’)
, to_date(’12-10-2003′,’DD-MM-YYYY’)
)
/
insert into activities
( label, start_date, end_date )
values
( ‘Acceptance Test’
, to_date(’10-09-2003′,’DD-MM-YYYY’)
, to_date(’22-03-2004′,’DD-MM-YYYY’)
)
/

And now for a little SQL query that returns the chart:

with periods as
( select label
, start_date
, end_date
from tngc_activities
)
, limits as — determine the earliest starting date and the latest end date to determine the overall width of the chart
( select min(start_date) period_start
, max(end_date) period_end
, 80 width — set the width as the number of characters
from periods
)
, bars as
( select lpad(label, ’20’)||’|’ activity
, (start_date – period_start)/(period_end – period_start) * width from_pos — the starting position for the bar
, (end_date – period_start)/(period_end – period_start) * width to_pos — the end position for the bar
from periods
, limits
)
select activity||
lpad(‘I’,from_pos)
||rpad(‘-‘, to_pos – from_pos, ‘-‘)
||’I’ gantt
from bars
union all
select rpad(‘_’,width + 22,’_’)
from limits
union all
select lpad(‘|’,21)
||to_char(period_start,’DD-MON-YYYY’)
||lpad(to_char(period_end,’DD-MON-YYYY’), width – 11)
from limits
/

2- Create BIP DM as below screenshot

3- The only difficulty with JFreeChart is that it doesn’t support XML dataset as input, there are ways around that limitation
You’ll need the following JAR files available on the web:
· jfreechart-1.0.4.jar
· jcommon-1.0.8.jar
· Commons-code-1.5.jar

you can download from commons-codec-1-5-sources-jar jfreechart-1-0-4 jcommon-1-0-8
The resulting JAR file will have to be copied to the location expected by Weblogic:

i ’ve created a Java class that will be packaged as a jar file and copied to the Weblogic folder. The purpose of this file is to:
1) Read in the XML from the BI-Publisher data model:
2) Organize XML data in the format expected by the JFreeChart API
3) Plot the Gantt Chart using the API
4) Return the generated image
5) BI-Publisher renders the image on the report
Update the code in Jdeveloper with the following is the code listing: this is a very crude code intended for testing only
 
/* ======================================
* JFreeChart : a free Java chart library
* ======================================
*
* Project Info: http://www.jfree.org/jfreechart/index.html
* Project Lead: David Gilbert (david.gilbert@object-refinery.com);
*
* (C) Copyright 2000-2003, by Object Refinery Limited and Contributors.
*
* This library is free software; you can redistribute it and/or modify it under the terms
* of the GNU Lesser General Public License as published by the Free Software Foundation;
* either version 2.1 of the License, or (at your option) any later version.
*
* This library is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY;
* without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
* See the GNU Lesser General Public License for more details.
*
* You should have received a copy of the GNU Lesser General Public License along with this
* library; if not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330,
* Boston, MA 02111-1307, USA.
package oracle.bip.extensions;
import java.awt.image.BufferedImage;
import java.io.IOException;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import org.apache.commons.codec.binary.Base64;
import javax.sql.rowset.serial.SerialException;
import javax.xml.parsers.ParserConfigurationException;
import org.jfree.chart.ChartFactory;
import org.jfree.chart.ChartPanel;
import org.jfree.chart.ChartRenderingInfo;
import org.jfree.chart.ChartUtilities;
import org.jfree.chart.JFreeChart;
import org.jfree.chart.entity.StandardEntityCollection;
import org.jfree.data.category.IntervalCategoryDataset;
import org.jfree.data.gantt.Task;
import org.jfree.data.gantt.TaskSeries;
import org.jfree.data.gantt.TaskSeriesCollection;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.xml.sax.SAXException;
public class bipExt {
public static String createGantt(NodeList bipXML, String taskName,
String taskStartDate,
String taskEndDate, String title) throws IOException,
SerialException,
ParserConfigurationException, SQLException, SAXException,
ParseException, NullPointerException {
final TaskSeries s1 = new TaskSeries(“GANTT”);
//Read in the start dates, end dates, task names as lists
ArrayList<String> startDate = new ArrayList<String>();
ArrayList<String> endDate = new ArrayList<String>();
ArrayList<String> taskname = new ArrayList<String>();
// First parameter list is the list of task start dates
for (int i = 0; i < bipXML.getLength(); i++) {
Node node = bipXML.item(i);
if (node.getNodeType() == Node.ELEMENT_NODE) {
Element element = (Element)node;
NodeList nodelist = element.getElementsByTagName(taskStartDate);
Element element1 = (Element)nodelist.item(0);
NodeList startDateNode = element1.getChildNodes();
String c1=startDateNode.item(0).getNodeValue().toString();
// Extract the canonical dates
startDate.add( c1.substring(0, 9));
}
}
// Second parameter list is the list of task end dates
for (int i = 0; i < bipXML.getLength(); i++) {
Node node = bipXML.item(i);
if (node.getNodeType() == Node.ELEMENT_NODE) {
Element element = (Element)node;
NodeList nodelist = element.getElementsByTagName(taskEndDate);
Element element1 = (Element)nodelist.item(0);
NodeList endDateNode = element1.getChildNodes();
String c2=endDateNode.item(0).getNodeValue().toString();
// Extract the canonical dates
endDate.add(c2.substring(0, 9));
}
}
// First parameter list is the list of task labels
for (int i = 0; i < bipXML.getLength(); i++) {
Node node = bipXML.item(i);
if (node.getNodeType() == Node.ELEMENT_NODE) {
Element element = (Element)node;
NodeList nodelist = element.getElementsByTagName(taskName);
Element element1 = (Element)nodelist.item(0);
NodeList labelNode = element1.getChildNodes();
String label = labelNode.item(0).getNodeValue();
taskname.add(label);
}
}
BufferedImage chartImage;
bipExt gantt = new bipExt();
// Create the dataset expected by JFreeChart for the Gantt Chart Type
IntervalCategoryDataset dataset =
gantt.createDataset(startDate, endDate, taskname);
// Create the JFreeChart
final JFreeChart chart = ChartFactory.createGanttChart(
title,
“Task”,
“Date”,
dataset,
true,
true,
false
);
chart.setBackgroundPaint(new GradientPaint(0, 0, Color.white, 1000, 0, Color.blue));
// Render the Chart as an image
final ChartPanel chartPanel = new ChartPanel(chart);
int width=450;
int height=270;
chartPanel.setPreferredSize(new java.awt.Dimension(width, height));
ChartRenderingInfo info = null;
info = new ChartRenderingInfo(new StandardEntityCollection());
chartImage = chart.createBufferedImage(550, 350, info);
byte[] buffered_image = ChartUtilities.encodeAsPNG(chartImage);
String image = new String(Base64.encodeBase64Chunked(buffered_image));
return image;
}
public bipExt() {
}
public static IntervalCategoryDataset createDataset(List sdate,
List edate,
List taskName) throws ParseException {
Iterator iterator = sdate.iterator();
final TaskSeries s1 = new TaskSeries(“Schedule”);
for (int i = 0; i < sdate.size(); i++) {
String c1;
String c2;
c1=sdate.get(i).toString();
c2=edate.get(i).toString();
s1.add(new Task(taskName.get(i).toString(), new SimpleDateFormat(“yyyy-MM-dd”).parse(c1),
new SimpleDateFormat(“yyyy-MM-dd”).parse(c2)));
}
final TaskSeriesCollection collection = new TaskSeriesCollection();
collection.add(s1);
return collection;
}
}

4- Create the BIP template, I used Template builder to create an RTF template with 3 fields

Namespace:
Create Gantt Chart: is the call to the custom Java code. My custom code call is:

I am basically feeding the XML data from BI-Publisher to the JFREECHART API as well as the XML tags
Display Gantt Chart: renders the resulting image (Gantt Chart) from JFreeChart;

5) Change the “Disable External References” flag to “FALSE” (it is TRUE by default) in BI-Publisher

6)  Upload your RTF template, bounce the services